
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Flynn.Core
{
    public class SqlServerVersionsTableVersioning : IVersioning
    {
        public int GetCurrentVersion(IDbConnection connection)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            int currentVersion;

            // TODO Do we have to cast here? Is there a way to solve this with generics?
            using (SqlCommand command = ((SqlConnection)connection).CreateCommand())
            {
                command.CommandText = GetCommandForGettingCurrentVersion();

                object version = command.ExecuteScalar();
                if (version == null)
                    return 0;

                currentVersion = (int)version;
            }

            return currentVersion;
        }

        public void PrepareDatabase(IDbConnection connection)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            // TODO Do we have to cast here? Is there a way to solve this with generics?
            using (SqlCommand command = (SqlCommand)connection.CreateCommand())
            {
                command.CommandText = GetCreateVersionsTableCommand();

                command.ExecuteNonQuery();
            }

            using (SqlCommand command = (SqlCommand)connection.CreateCommand())
            {
                command.CommandText = GetInsertInitialVersionCommand();

                command.ExecuteNonQuery();
            }
        }

        public void SetCurrentVersion(int currentVersion, IDbConnection connection)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            // TODO Do we have to cast here? Is there a way to solve this with generics?
            using (SqlCommand command = ((SqlConnection)connection).CreateCommand())
            {
                command.CommandText = GetCommandForSettingVersion();
                command.Parameters.AddWithValue("@Version", currentVersion);

                command.ExecuteNonQuery();
            }
        }

        private static string GetCreateVersionsTableCommand()
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("CREATE TABLE [dbo].[Versions] (");
            sb.AppendLine("    [Id] int IDENTITY(1, 1) NOT NULL,");
            sb.AppendLine("    [Version] int NOT NULL,");
            sb.AppendLine("    CONSTRAINT [PK_Versions] PRIMARY KEY CLUSTERED");
            sb.AppendLine("    (");
            sb.AppendLine("         [Id] ASC");
            sb.AppendLine("    ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]");
            sb.AppendLine(") ON [PRIMARY]");

            return sb.ToString();
        }

        private static string GetInsertInitialVersionCommand()
        {
            return "INSERT INTO [Versions] ([Version]) VALUES (0)";
        }

        private static string GetCommandForGettingCurrentVersion()
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("select top 1 [Version]");
            sb.AppendLine("from [dbo].[Versions]");
            sb.AppendLine("order by [Id] desc");

            return sb.ToString();
        }

        private static string GetCommandForSettingVersion()
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("insert into [dbo].[Versions]");
            sb.AppendLine("    ([Version])");
            sb.AppendLine("values");
            sb.AppendLine("    (@Version)");

            return sb.ToString();
        }
    }
}